from sqlalchemy.dialects.mysql import LONGTEXT
import pandas as pd
import os
from sqlalchemy import create_engine,MetaData
from sqlalchemy_utils import create_database,database_exists
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Table,Column,Integer,ForeignKey,String,Text,FLOAT,VARCHAR
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.ext.declarative import declarative_base
import numpy as np
def save_to_sql(dataframe,mode='append'):
    engine = create_engine('mysql+pymysql://root:a123456a@localhost:3306/firm', echo=False)
    db = SQLAlchemy()
    dtypedict = {

        'stkcd': VARCHAR(150),

        'title': VARCHAR(150),

        'link':  VARCHAR(150),

        '公告类型':  VARCHAR(4000),

        'enddate':  VARCHAR(150),

        'text':  LONGTEXT()
    }
    dataframe.to_sql(name='base_imformation',con=engine,index=False,if_exists=mode, dtype=dtypedict)
if __name__=="__main__":
    files=os.listdir('D:\公司年报爬取\上传失败')
    for file in files:
        dataframe=pd.read_excel('D:\公司年报爬取\上传失败'+'\\'+file)
        dataframe=dataframe.iloc[:,1:]
        dataframe=dataframe.fillna('未知')
        dataframe['text'] = np.nan
        try:
            save_to_sql(dataframe)
            print('cg')
            os.remove('D:\公司年报爬取\上传失败'+'\\'+file)
        except:
            print('wenti')

